﻿Imports TNCDAL
Imports Excel = Microsoft.Office.Interop.Excel

Public Class CModule_Sy


    Public Shared Sub ToExcel_INV_Customs(ByVal ID_HBL As Integer)
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\Inv_Custom_template.xltx")
        xlSheet = xlBook.Worksheets("INV CUSTOMS")
        xlApp.Visible = True

        Dim HBL = (From p In db.tblHBLs
                    Where p.ID_HBL = ID_HBL
                    Select p).Single

        'Get File
        Dim File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

        Dim Shipper As String = ""
        Dim Customer As String = ""

        Dim ComercialInvoice = (From p In db.tblComercialInvoices
                                Where p.ID_HBL = ID_HBL
                                Select p).SingleOrDefault
        ''0 COMERCIAL INVOICE
        xlSheet.Cells(6, 5) = ComercialInvoice.ComercialInvoiceNo
        xlSheet.Cells(7, 5) = ComercialInvoice.CreateDate

        If HBL.IsMaster Then
            'MBL
            '1. SHIPPER
            xlSheet.Cells(1, 1) = File.tblCompany.Name
            xlSheet.Cells(2, 1) = File.tblCompany.Address
            xlSheet.Cells(3, 1) = "TEL: " + File.tblCompany.Tel + "     FAX: " + File.tblCompany.Fax

            '2. CONSIGNEE ' TBLFOREIGNCOMPANY
            xlSheet.Cells(10, 2) = File.tblForeignCompany.Name
            xlSheet.Cells(11, 2) = File.tblForeignCompany.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblForeignCompany.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblForeignCompany.Fax

        Else
            'HBL SHIPPER = TAN NAM CHINH
            '1. SHIPPER
            xlSheet.Cells(1, 1) = "TAN NAM CHINH TRADING AND SERVICE CO., LTD"
            xlSheet.Cells(2, 1) = "483/67 LE VAN SY ST., DIST 3, HOCHIMINH CITY, VIETNAM"
            xlSheet.Cells(3, 1) = "TEL: 848-39432369 / 70" + "     FAX : 848-38265731"

            '2. CONSIGNEE ' TBLAGENT
            xlSheet.Cells(10, 2) = File.tblAgent.Name
            xlSheet.Cells(11, 2) = File.tblAgent.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblAgent.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblAgent.Fax
        End If




        '5. VESSEL/VOYAGE
        Dim Voyage = (From p In db.tblVoyages
                     Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = HBL.tblFileMBL.tblMBL.ID_MBL Select q.ID_Voyage).Single
                     Select p).SingleOrDefault

        'VESSEL
        'xlSheet.Cells(19, 4) = Voyage.tblVessel.VesselName + "/" + Voyage.VoyageNo
        'LINE BOOKING NO
        'xlSheet.Cells(19, 7) = File.LineBookingNo
        'POL
        xlSheet.Cells(14, 2) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName.ToUpper
        'POD
        xlSheet.Cells(15, 2) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName.ToUpper
        'DELIVERY TO:
        xlSheet.Cells(14, 4) = "DELIVERY TO: " + ComercialInvoice.DeliveryTo
        xlSheet.Cells(18, 5) = "CNF " + ComercialInvoice.DeliveryTo
        xlSheet.Cells(15, 4) = "COUNTRY OF ORIGIN: " + ComercialInvoice.tblCountry.CountryName.ToUpper
        Dim O_F As Double = 0
        O_F = (From p In db.tblFileFees
             Where p.ID_File = File.ID_File And p.ID_Fee = 1
             Select p.Price).SingleOrDefault

        'GIA MUA OF
        xlSheet.Cells(20, 6) = O_F

        'xlSheet.Cells(21, 1) = "ETD: " + Voyage.ETD.Value.Month.ToString + " " + Voyage.ETD.Value.Day.ToString + " " + Voyage.ETD.Value.Year.ToString
        'xlSheet.Cells(22, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        'xlSheet.Cells(21, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName

        ''6. MARK NUMBER
        'xlSheet.Cells(26, 2) = HBL.MarkNumber
        'mRange = xlSheet.Range(xlSheet.Cells(26, 2), xlSheet.Cells(39, 2))
        'mRange.Merge()
        'mRange.WrapText = True

        ''7 DECRIPTION OF GOOD
        'xlSheet.Cells(26, 4) = HBL.GoodDescription
        'mRange = xlSheet.Range(xlSheet.Cells(26, 4), xlSheet.Cells(39, 4))
        'mRange.Merge()
        'mRange.WrapText = True

        ''GW - NW
        'xlSheet.Cells(26, 7) = CDbl(HBL.GW).ToString("#,###")
        'xlSheet.Cells(26, 8) = CDbl(HBL.NW).ToString("#,###")
        'xlSheet.Cells(26, 9) = CDbl(HBL.CBM).ToString("#,###.00")

        '6. CONT/SEAL/CONTTYPE
        'Dim HBL_Cont = From p In db.tblConts
        '               Where p.ID_HBL = HBL.ID_HBL
        '               Select p

        'Dim numOfPackage As Double = 0
        'Dim numOfVolume As Double = 0


        'Dim i As Integer = 0
        'Dim Packing As String = "PACKING: "
        'Dim unitName As String = ""
        'Dim unitNW_GW As String = ""

        'For Each cont As tblCont In HBL_Cont
        '    numOfPackage += cont.Package
        '    numOfVolume += cont.CBM
        '    Packing += cont.tblLineCont.tblContType.ContTypeName + "/" + CDbl(cont.Package).ToString("#,###") + " " + cont.tblUnit.UnitName.ToUpper + ", "
        '    unitName = cont.tblUnit.UnitName
        '    unitNW_GW = "(" + CDbl(cont.Unit_NW).ToString("#,###.00") + "KGS(NW); " + CDbl(cont.Unit_GW).ToString("#,###.00") + "KGS(NW)/" + unitName + ")"
        '    xlSheet.Cells(14 + i, 7) = cont.tblLineCont.ContNo + "/" + cont.Seal

        '    i += 1
        'Next

        'Packing = Packing.Substring(0, Packing.Length - 2)


        'xlSheet.Cells(15, 6) = "COUNTRY OF ORIGIN: " + ComercialInvoice.tblCountry.CountryName.ToUpper

        xlSheet.Cells(19, 3) = CDbl(HBL.NW).ToString("#,###.00")
        xlSheet.Cells(19, 5) = CDbl(ComercialInvoice.UnitTermPriceHQ).ToString("#,###.00")
        Dim Amount As Double = ComercialInvoice.UnitTermPriceHQ * HBL.NW
        xlSheet.Cells(19, 6) = Amount.ToString("#,###.00")

        Dim total_amount As Double = Amount + O_F
        xlSheet.Cells(21, 6) = total_amount.ToString("#,###.00")

        Dim dollar As Double = 0
        Dim cen As Double = 0
        Dim amount_text() As String
        amount_text = total_amount.ToString("#,###.00").Split(".")
        dollar = Math.Floor(total_amount)
        cen = CDbl(amount_text(1))

        Dim TienBangChu As String = ""
        If cen > 0 Then
            TienBangChu = NumToStringEnglish(dollar).ToUpper + " & CENTS" + NumToStringEnglish(cen).ToUpper + " ONLY"
        Else
            TienBangChu = NumToStringEnglish(dollar).ToUpper + " ONLY"
        End If

        xlSheet.Cells(23, 1) = "SAY: US DOLLARS " + TienBangChu
    End Sub

    Public Shared Sub ToExcel_INV_CNEE(ByVal ID_HBL As Integer)
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\Inv_CNEE_template.xltx")
        xlSheet = xlBook.Worksheets("INV CNEE")
        xlApp.Visible = True

        Dim HBL = (From p In db.tblHBLs
                    Where p.ID_HBL = ID_HBL
                    Select p).Single

        'Get File
        Dim File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

        Dim Shipper As String = ""
        Dim Customer As String = ""

        Dim ComercialInvoice = (From p In db.tblComercialInvoices
                                Where p.ID_HBL = ID_HBL
                                Select p).SingleOrDefault
        ''0 COMERCIAL INVOICE
        xlSheet.Cells(9, 8) = ComercialInvoice.ComercialInvoiceNo
        xlSheet.Cells(10, 8) = ComercialInvoice.CreateDate

        If HBL.IsMaster Then
            'MBL
            '1. SHIPPER
            xlSheet.Cells(1, 1) = File.tblCompany.Name
            xlSheet.Cells(2, 1) = File.tblCompany.Address
            xlSheet.Cells(3, 1) = "TEL: " + File.tblCompany.Tel + "     FAX: " + File.tblCompany.Fax

            '2. CONSIGNEE ' TBLFOREIGNCOMPANY
            xlSheet.Cells(9, 1) = File.tblForeignCompany.Name
            xlSheet.Cells(10, 1) = File.tblForeignCompany.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblForeignCompany.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblForeignCompany.Fax

        Else
            'HBL SHIPPER = TAN NAM CHINH
            '1. SHIPPER
            xlSheet.Cells(1, 1) = "TAN NAM CHINH TRADING AND SERVICE CO., LTD"
            xlSheet.Cells(2, 1) = "483/67 LE VAN SY ST., DIST 3, HOCHIMINH CITY, VIETNAM"
            xlSheet.Cells(3, 1) = "TEL: 848-39432369 / 70" + "     FAX : 848-38265731"

            '2. CONSIGNEE ' TBLAGENT
            xlSheet.Cells(9, 1) = File.tblAgent.Name
            xlSheet.Cells(10, 1) = File.tblAgent.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblAgent.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblAgent.Fax
        End If

        


        '5. VESSEL/VOYAGE
        Dim Voyage = (From p In db.tblVoyages
                     Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = HBL.tblFileMBL.tblMBL.ID_MBL Select q.ID_Voyage).Single
                     Select p).SingleOrDefault

        'VESSEL
        xlSheet.Cells(19, 4) = Voyage.tblVessel.VesselName + "/" + Voyage.VoyageNo
        'LINE BOOKING NO
        xlSheet.Cells(19, 7) = File.LineBookingNo
        'POL
        xlSheet.Cells(21, 1) = Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName.ToUpper
        'POD
        xlSheet.Cells(21, 4) = Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName.ToUpper
        'DELIVERY TO:
        xlSheet.Cells(21, 7) = ComercialInvoice.DeliveryTo
        'xlSheet.Cells(21, 1) = "ETD: " + Voyage.ETD.Value.Month.ToString + " " + Voyage.ETD.Value.Day.ToString + " " + Voyage.ETD.Value.Year.ToString
        'xlSheet.Cells(22, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        'xlSheet.Cells(21, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName

        ''6. MARK NUMBER
        'xlSheet.Cells(26, 2) = HBL.MarkNumber
        'mRange = xlSheet.Range(xlSheet.Cells(26, 2), xlSheet.Cells(39, 2))
        'mRange.Merge()
        'mRange.WrapText = True

        ''7 DECRIPTION OF GOOD
        'xlSheet.Cells(26, 4) = HBL.GoodDescription
        'mRange = xlSheet.Range(xlSheet.Cells(26, 4), xlSheet.Cells(39, 4))
        'mRange.Merge()
        'mRange.WrapText = True

        ''GW - NW
        'xlSheet.Cells(26, 7) = CDbl(HBL.GW).ToString("#,###")
        'xlSheet.Cells(26, 8) = CDbl(HBL.NW).ToString("#,###")
        'xlSheet.Cells(26, 9) = CDbl(HBL.CBM).ToString("#,###.00")

        '6. CONT/SEAL/CONTTYPE
        Dim HBL_Cont = From p In db.tblConts
                       Where p.ID_HBL = HBL.ID_HBL
                       Select p

        Dim numOfPackage As Double = 0
        Dim numOfVolume As Double = 0

        Dim NumInsert As Integer = 0
        If HBL_Cont.Count > 2 Then
            NumInsert = HBL_Cont.Count - 2
        End If

        For j As Integer = 1 To NumInsert
            xlSheet.Rows(16).Insert()
        Next


        '3. NOTIFY PARTY
        Dim NotifyParty As String() = HBL.NotifyParty.Split(Chr(10))
        If NotifyParty.Count > 1 Then
            Try
                xlSheet.Cells(14, 1) = NotifyParty(0)
                xlSheet.Cells(15, 1) = NotifyParty(1).Substring(8, NotifyParty(1).Length - 8)
                'xlSheet.Cells(14, 6) =
                xlSheet.Cells(17, 1) = "TEL: " + NotifyParty(2).Substring(5, NotifyParty(2).Length - 5) + ";   FAX: " + NotifyParty(3).Substring(5, NotifyParty(3).Length - 5)
            Catch ex As Exception

            End Try
        Else
            xlSheet.Cells(14, 1) = NotifyParty(0)
        End If


        Dim i As Integer = 0
        Dim Packing As String = "PACKING: "
        Dim unitName As String = ""
        Dim unitNW_GW As String = ""

        For Each cont As tblCont In HBL_Cont
            numOfPackage += cont.Package
            numOfVolume += cont.CBM
            Packing += cont.tblContType.ContTypeName + "/" + CDbl(cont.Package).ToString("#,###") + " " + cont.tblUnit.UnitName.ToUpper + ", "
            unitName = cont.tblUnit.UnitName
            'unitNW_GW = "(" + CDbl(cont.Unit_NW).ToString("#,###.00") + "KGS(NW); " + CDbl(cont.Unit_GW).ToString("#,###.00") + "KGS(NW)/" + unitName + ")"
            xlSheet.Cells(14 + i, 7) = cont.ContNo + "/" + cont.Seal

            i += 1
        Next

        xlSheet.Cells(16 + NumInsert, 7) = ComercialInvoice.DescriptionOfCont
        xlSheet.Cells(17 + NumInsert, 7) = "MADE IN " + ComercialInvoice.tblCountry.CountryName.ToUpper
        'mRange = xlSheet.Range(xlSheet.Cells(15 + NumInsert, 11), xlSheet.Cells(15 + NumInsert, 12))
        'mRange.Merge()
        'mRange.HorizontalAlignment = HorizontalAlignment.Center

        'mRange = xlSheet.Range(xlSheet.Cells(16 + NumInsert, 11), xlSheet.Cells(16 + NumInsert, 12))
        'mRange.Merge()
        'mRange.HorizontalAlignment = HorizontalAlignment.Center

        Packing = Packing.Substring(0, Packing.Length - 2)


        'xlSheet.Cells(15, 6) = "COUNTRY OF ORIGIN: " + ComercialInvoice.tblCountry.CountryName.ToUpper

        xlSheet.Cells(22 + NumInsert, 5) = "Number of " + unitName.ToLower
        xlSheet.Cells(26 + NumInsert, 5) = numOfPackage
        xlSheet.Cells(26 + NumInsert, 6) = CDbl(HBL.NW).ToString("#,###.00")
        xlSheet.Cells(26 + NumInsert, 7) = CDbl(ComercialInvoice.UnitTermPriceCNEE).ToString("#,###.00")
        xlSheet.Cells(26 + NumInsert, 8) = CDbl(ComercialInvoice.UnitTermPriceCNEE * HBL.NW).ToString("#,###.00")

        xlSheet.Cells(27 + NumInsert, 2) = unitNW_GW
        'total
        xlSheet.Cells(29 + NumInsert, 5) = numOfPackage
        xlSheet.Cells(29 + NumInsert, 6) = CDbl(HBL.NW).ToString("#,###.00")
        Dim Amount As Double = ComercialInvoice.UnitTermPriceCNEE * HBL.NW
        xlSheet.Cells(29 + NumInsert, 8) = Amount.ToString("#,###.00")
        '
        Dim dollar As Double = 0
        Dim cen As Double = 0
        Dim amount_text() As String
        amount_text = Amount.ToString("#,###.00").Split(".")
        dollar = Math.Floor(Amount)
        cen = CDbl(amount_text(1))

        Dim TienBangChu As String = ""
        If cen > 0 Then
            TienBangChu = CModule_Sy.NumToStringEnglish(dollar).ToUpper + " & CENTS" + NumToStringEnglish(cen).ToUpper + " ONLY"
        Else
            TienBangChu = CModule_Sy.NumToStringEnglish(dollar).ToUpper + " ONLY"
        End If

        xlSheet.Cells(30 + NumInsert, 1) = "SAY: US DOLLARS " + TienBangChu
        xlSheet.Cells(31 + NumInsert, 1) = "TOTAL: " + CDbl(numOfPackage).ToString("#,###.00") + unitName.ToUpper
        xlSheet.Cells(32 + NumInsert, 1) = "TOTAL WEIGHT: " + CDbl(HBL.NW).ToString("#,###.00") + " KGS(NW); " + CDbl(HBL.GW).ToString("#,###.00") + " KGS(GW)"
        xlSheet.Cells(33 + NumInsert, 1) = "TOTAL VOLUME: " + CDbl(numOfVolume).ToString("#,###.00") + " M3"


    End Sub
    'Inv_Custom_template

    Public Shared Sub ToExcel_PL_CNEE(ByVal ID_HBL As Integer)
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\PackingList_CNEE_template.xltx")
        xlSheet = xlBook.Worksheets("PL CNEE")
        xlApp.Visible = True
        xlBook.Activate()

        Dim HBL = (From p In db.tblHBLs
                    Where p.ID_HBL = ID_HBL
                    Select p).Single

        'Get File
        Dim File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

        Dim Shipper As String = ""
        Dim Customer As String = ""

        Dim ComercialInvoice = (From p In db.tblComercialInvoices
                                Where p.ID_HBL = ID_HBL
                                Select p).SingleOrDefault
        ''0 COMERCIAL INVOICE
        xlSheet.Cells(7, 12) = ComercialInvoice.ComercialInvoiceNo
        xlSheet.Cells(8, 12) = ComercialInvoice.CreateDate

        If HBL.IsMaster Then
            'MBL
            '1. SHIPPER
            xlSheet.Cells(1, 1) = File.tblCompany.Name
            xlSheet.Cells(2, 1) = File.tblCompany.Address
            xlSheet.Cells(3, 1) = "TEL: " + File.tblCompany.Tel + "     FAX: " + File.tblCompany.Fax

            '2. CONSIGNEE ' TBLFOREIGNCOMPANY
            xlSheet.Cells(8, 1) = File.tblForeignCompany.Name
            xlSheet.Cells(9, 1) = File.tblForeignCompany.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblForeignCompany.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblForeignCompany.Fax

        Else
            'HBL SHIPPER = TAN NAM CHINH
            '1. SHIPPER
            xlSheet.Cells(1, 1) = "TAN NAM CHINH TRADING AND SERVICE CO., LTD"
            xlSheet.Cells(2, 1) = "483/67 LE VAN SY ST., DIST 3, HOCHIMINH CITY, VIETNAM"
            xlSheet.Cells(3, 1) = "TEL: 848-39432369 / 70" + "     FAX : 848-38265731"

            '2. CONSIGNEE ' TBLAGENT
            xlSheet.Cells(8, 1) = File.tblAgent.Name
            xlSheet.Cells(9, 1) = File.tblAgent.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblAgent.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblAgent.Fax
        End If

        '5. VESSEL/VOYAGE
        Dim Voyage = (From p In db.tblVoyages
                     Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = HBL.tblFileMBL.tblMBL.ID_MBL Select q.ID_Voyage).Single
                     Select p).SingleOrDefault

        'VESSEL
        xlSheet.Cells(18, 8) = Voyage.tblVessel.VesselName + "/" + Voyage.VoyageNo
        'LINE BOOKING NO
        xlSheet.Cells(18, 11) = File.LineBookingNo
        'POL
        xlSheet.Cells(20, 1) = Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName.ToUpper
        'POD
        xlSheet.Cells(20, 8) = Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName.ToUpper
        'DELIVERY TO:
        xlSheet.Cells(20, 11) = ComercialInvoice.DeliveryTo
        'xlSheet.Cells(21, 1) = "ETD: " + Voyage.ETD.Value.Month.ToString + " " + Voyage.ETD.Value.Day.ToString + " " + Voyage.ETD.Value.Year.ToString
        'xlSheet.Cells(22, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        'xlSheet.Cells(21, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName

        ''6. MARK NUMBER
        'xlSheet.Cells(26, 2) = HBL.MarkNumber
        'mRange = xlSheet.Range(xlSheet.Cells(26, 2), xlSheet.Cells(39, 2))
        'mRange.Merge()
        'mRange.WrapText = True

        ''7 DECRIPTION OF GOOD
        'xlSheet.Cells(26, 4) = HBL.GoodDescription
        'mRange = xlSheet.Range(xlSheet.Cells(26, 4), xlSheet.Cells(39, 4))
        'mRange.Merge()
        'mRange.WrapText = True

        ''GW - NW
        'xlSheet.Cells(26, 7) = CDbl(HBL.GW).ToString("#,###")
        'xlSheet.Cells(26, 8) = CDbl(HBL.NW).ToString("#,###")
        'xlSheet.Cells(26, 9) = CDbl(HBL.CBM).ToString("#,###.00")

        '6. CONT/SEAL/CONTTYPE
        Dim HBL_Cont = From p In db.tblConts
                       Where p.ID_HBL = HBL.ID_HBL
                       Select p

        Dim numOfPackage As Double = 0
        Dim numOfVolume As Double = 0

        Dim NumInsert As Integer = 0
        If HBL_Cont.Count > 3 Then
            NumInsert = HBL_Cont.Count - 3
        End If

        For j As Integer = 1 To NumInsert
            xlSheet.Rows(14).Insert()
        Next


        '3. NOTIFY PARTY
        Dim NotifyParty As String() = HBL.NotifyParty.Split(Chr(10))
        If NotifyParty.Count > 1 Then
            Try
                xlSheet.Cells(12, 1) = NotifyParty(0)
                xlSheet.Cells(13, 1) = NotifyParty(1).Substring(8, NotifyParty(1).Length - 8)
                'xlSheet.Cells(14, 6) =
                xlSheet.Cells(15, 1) = "TEL: " + NotifyParty(2).Substring(5, NotifyParty(2).Length - 5) + ";   FAX: " + NotifyParty(3).Substring(5, NotifyParty(3).Length - 5)
            Catch ex As Exception

            End Try
        Else
            xlSheet.Cells(12, 1) = NotifyParty(0)
        End If

        Dim i As Integer = 0
        Dim Packing As String = "PACKING: "
        Dim unitName As String = ""
        Dim unitNW_GW As String = ""

        For Each cont As tblCont In HBL_Cont
            numOfPackage += cont.Package
            numOfVolume += cont.CBM
            Packing += cont.tblContType.ContTypeName + "/" + CDbl(cont.Package).ToString("#,###") + " " + cont.tblUnit.UnitName.ToUpper + ", "
            unitName = cont.tblUnit.UnitName
            'unitNW_GW = "(" + CDbl(cont.Unit_NW).ToString("#,###.00") + "KGS(NW); " + CDbl(cont.Unit_GW).ToString("#,###.00") + "KGS(NW)/" + unitName + ")"
            xlSheet.Cells(12 + i, 11) = cont.ContNo + "/" + cont.Seal

            i += 1
        Next

        xlSheet.Cells(15 + NumInsert, 11) = ComercialInvoice.DescriptionOfCont
        xlSheet.Cells(16 + NumInsert, 11) = "MADE IN " + ComercialInvoice.tblCountry.CountryName.ToUpper
        mRange = xlSheet.Range(xlSheet.Cells(15 + NumInsert, 11), xlSheet.Cells(15 + NumInsert, 12))
        mRange.Merge()
        mRange.HorizontalAlignment = HorizontalAlignment.Center

        mRange = xlSheet.Range(xlSheet.Cells(16 + NumInsert, 11), xlSheet.Cells(16 + NumInsert, 12))
        mRange.Merge()
        mRange.HorizontalAlignment = HorizontalAlignment.Center

        Packing = Packing.Substring(0, Packing.Length - 2)


        'xlSheet.Cells(15, 6) = "COUNTRY OF ORIGIN: " + ComercialInvoice.tblCountry.CountryName.ToUpper

        xlSheet.Cells(23 + NumInsert, 10) = "Number of " + unitName.ToLower
        xlSheet.Cells(24 + NumInsert, 10) = numOfPackage
        xlSheet.Cells(24 + NumInsert, 11) = CDbl(HBL.NW).ToString("#,###.00")
        xlSheet.Cells(24 + NumInsert, 12) = CDbl(HBL.GW).ToString("#,###.00")

        xlSheet.Cells(25 + NumInsert, 2) = unitNW_GW

        'total
        xlSheet.Cells(26 + NumInsert, 10) = numOfPackage
        xlSheet.Cells(26 + NumInsert, 11) = CDbl(HBL.NW).ToString("#,###.00")
        xlSheet.Cells(26 + NumInsert, 12) = CDbl(HBL.GW).ToString("#,###.00")
        '

        xlSheet.Cells(27 + NumInsert, 1) = "TOTAL: " + CDbl(numOfPackage).ToString("#,###.00") + unitName.ToUpper
        xlSheet.Cells(28 + NumInsert, 1) = "TOTAL WEIGHT: " + CDbl(HBL.NW).ToString("#,###.00") + " KGS(NW); " + CDbl(HBL.GW).ToString("#,###.00") + " KGS(GW)"
        xlSheet.Cells(29 + NumInsert, 1) = "TOTAL VOLUME: " + CDbl(numOfVolume).ToString("#,###.00") + " M3"


    End Sub

    Public Shared Sub ToExcel_PL(ByVal ID_HBL As Integer)
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\PackingList_template.xltx")
        xlSheet = xlBook.Worksheets("PL")
        xlApp.Visible = True

        Dim HBL = (From p In db.tblHBLs
                    Where p.ID_HBL = ID_HBL
                    Select p).Single

        'Get File
        Dim File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

        Dim Shipper As String = ""
        Dim Customer As String = ""

        Dim ComercialInvoice = (From p In db.tblComercialInvoices
                                Where p.ID_HBL = ID_HBL
                                Select p).SingleOrDefault
        ''0 COMERCIAL INVOICE
        xlSheet.Cells(6, 8) = ComercialInvoice.ComercialInvoiceNo
        xlSheet.Cells(7, 8) = ComercialInvoice.CreateDate

        If HBL.IsMaster Then
            'MBL
            '1. SHIPPER
            xlSheet.Cells(1, 1) = File.tblCompany.Name
            xlSheet.Cells(2, 1) = File.tblCompany.Address
            xlSheet.Cells(3, 1) = "TEL: " + File.tblCompany.Tel + "     FAX: " + File.tblCompany.Fax

            '2. CONSIGNEE ' TBLFOREIGNCOMPANY
            xlSheet.Cells(10, 1) = File.tblForeignCompany.Name
            xlSheet.Cells(11, 1) = File.tblForeignCompany.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblForeignCompany.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblForeignCompany.Fax

        Else
            'HBL SHIPPER = TAN NAM CHINH
            '1. SHIPPER
            xlSheet.Cells(1, 1) = "TAN NAM CHINH TRADING AND SERVICE CO., LTD"
            xlSheet.Cells(2, 1) = "483/67 LE VAN SY ST., DIST 3, HOCHIMINH CITY, VIETNAM"
            xlSheet.Cells(3, 1) = "TEL: 848-39432369 / 70" + "     FAX : 848-38265731"

            '2. CONSIGNEE ' TBLAGENT
            xlSheet.Cells(10, 1) = File.tblAgent.Name
            xlSheet.Cells(11, 1) = File.tblAgent.Address
            'xlSheet.Cells(16, 2) = "Tel: " + File.tblAgent.Tel
            'xlSheet.Cells(17, 2) = "Fax: " + File.tblAgent.Fax
        End If

        ''3. NOTIFY PARTY
        'Dim NotifyParty As String() = HBL.NotifyParty.Split(Chr(10))
        'Try
        '    xlSheet.Cells(14, 6) = NotifyParty(0)
        '    xlSheet.Cells(15, 6) = NotifyParty(1)
        '    xlSheet.Cells(16, 6) = NotifyParty(2)
        '    xlSheet.Cells(17, 6) = NotifyParty(3)
        'Catch ex As Exception

        'End Try

        '5. VESSEL/VOYAGE
        Dim Voyage = (From p In db.tblVoyages
                     Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = HBL.tblFileMBL.tblMBL.ID_MBL Select q.ID_Voyage).Single
                     Select p).SingleOrDefault

        'xlSheet.Cells(24, 2) = Voyage.tblVessel.VesselName + "/" + Voyage.VoyageNo
        'POL
        xlSheet.Cells(14, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName.ToUpper
        'POD
        xlSheet.Cells(15, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName.ToUpper
        
        'xlSheet.Cells(21, 1) = "ETD: " + Voyage.ETD.Value.Month.ToString + " " + Voyage.ETD.Value.Day.ToString + " " + Voyage.ETD.Value.Year.ToString
        'xlSheet.Cells(22, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        'xlSheet.Cells(21, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName

        ''6. MARK NUMBER
        'xlSheet.Cells(26, 2) = HBL.MarkNumber
        'mRange = xlSheet.Range(xlSheet.Cells(26, 2), xlSheet.Cells(39, 2))
        'mRange.Merge()
        'mRange.WrapText = True

        ''7 DECRIPTION OF GOOD
        'xlSheet.Cells(26, 4) = HBL.GoodDescription
        'mRange = xlSheet.Range(xlSheet.Cells(26, 4), xlSheet.Cells(39, 4))
        'mRange.Merge()
        'mRange.WrapText = True

        ''GW - NW
        'xlSheet.Cells(26, 7) = CDbl(HBL.GW).ToString("#,###")
        'xlSheet.Cells(26, 8) = CDbl(HBL.NW).ToString("#,###")
        'xlSheet.Cells(26, 9) = CDbl(HBL.CBM).ToString("#,###.00")

        '6. CONT/SEAL/CONTTYPE
        Dim HBL_Cont = From p In db.tblConts
                       Where p.ID_HBL = HBL.ID_HBL
                       Select p

        Dim numOfPackage As Double = 0


        Dim i As Integer = 0
        Dim Packing As String = "PACKING: "
        Dim unitName As String = ""
        For Each cont As tblCont In HBL_Cont
            numOfPackage += cont.Package
            Packing += cont.tblContType.ContTypeName + "/" + CDbl(cont.Package).ToString("#,###") + " " + cont.tblUnit.UnitName.ToUpper + ", "
            unitName = cont.tblUnit.UnitName
            'xlSheet.Cells(41 + i, 2) = i + 1
            'xlSheet.Cells(41 + i, 3) = cont.tblLineCont.ContNo.ToUpper
            'xlSheet.Cells(41 + i, 4) = cont.tblLineCont.tblContType.ContTypeName.ToUpper
            'xlSheet.Cells(41 + i, 5) = cont.Seal.ToUpper
            'xlSheet.Cells(41 + i, 6) = cont.GW
            'xlSheet.Cells(41 + i, 7) = cont.NW
            'xlSheet.Cells(41 + i, 8) = cont.CBM
            'Try
            '    xlSheet.Cells(41 + i, 9) = cont.Package
            '    xlSheet.Cells(41 + i, 10) = cont.tblUnit.UnitName
            'Catch ex As Exception

            'End Try
            'mRange = xlSheet.Range(xlSheet.Cells(41 + i, 6), xlSheet.Cells(41 + i, 9))
            'mRange.NumberFormatLocal = "#,###"
            i += 1
        Next


        Packing = Packing.Substring(0, Packing.Length - 2)

        xlSheet.Cells(14, 6) = "DELIVERY TO: " + ComercialInvoice.DeliveryTo
        xlSheet.Cells(15, 6) = "COUNTRY OF ORIGIN: " + ComercialInvoice.tblCountry.CountryName.ToUpper

        xlSheet.Cells(17, 6) = "NUMBER OF " + unitName.ToUpper
        xlSheet.Cells(18, 6) = numOfPackage
        xlSheet.Cells(18, 9) = CDbl(HBL.NW).ToString("#,###.00")
        xlSheet.Cells(18, 10) = CDbl(HBL.GW).ToString("#,###.00")
        'total
        xlSheet.Cells(19, 6) = numOfPackage
        xlSheet.Cells(19, 9) = CDbl(HBL.NW).ToString("#,###.00")
        xlSheet.Cells(19, 10) = CDbl(HBL.GW).ToString("#,###.00")

        xlSheet.Cells(21, 1) = Packing
        xlSheet.Cells(22, 1) = "NW: " + CDbl(HBL.NW).ToString("#,###.00") + " KGS   GW: " + CDbl(HBL.GW).ToString("#,###.00") + " KGS"


    End Sub

    'Public Shared Sub ChuyenSoLieuTuExcel()
    '    Dim db As New TanNamChinhDataContext

    '    'Chuyen so lieu tu excel sang store and don vi
    '    Dim oXL As Excel.Application
    '    Dim oWb As Excel.Workbook
    '    Dim oSheetHSLL As Excel.Worksheet

    '    oXL = CreateObject("Excel.Application")
    '    'mở file excel
    '    Dim OpenFileDialog1 As New OpenFileDialog

    '    If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
    '        oWb = oXL.Workbooks.Open(OpenFileDialog1.FileName)
    '        oSheetHSLL = oWb.Sheets("SOLIEU")
    '        'oXL.Visible = True
    '        'thêm Column vào bảng
    '        Dim loai As Integer = 0 ' 1 company_import, 2 company_export, 3 company_import_export, 4 consignee, 5 agent

    '        For i As Integer = 2 To 26 'SO ROW MAX
    '            Dim NAME As String = ""
    '            Dim ADDRESS As String = ""
    '            Dim TEL As String = ""
    '            Dim FAX As String = ""
    '            Dim cus As String = ""

    '            cus = oSheetHSLL.Cells(i, 2).Value
    '            NAME = oSheetHSLL.Cells(i, 3).Value
    '            ADDRESS = oSheetHSLL.Cells(i, 4).Value
    '            TEL = oSheetHSLL.Cells(i, 5).Value
    '            FAX = oSheetHSLL.Cells(i, 6).Value

    '            Try
    '                cus = cus.Trim
    '                NAME = NAME.Trim
    '                ADDRESS = ADDRESS.Trim
    '                TEL = TEL.Trim
    '                FAX = FAX.Trim
    '            Catch ex As Exception

    '            End Try


    '            Try
    '                If NAME = "CUS_IMPORT" Then
    '                    loai = 1
    '                    Continue For
    '                ElseIf NAME = "CUS_EXPORT" Then
    '                    loai = 2
    '                    Continue For
    '                ElseIf NAME = "CUS_IMPORT_EXPORT" Then
    '                    loai = 3
    '                    Continue For
    '                ElseIf NAME = "CONSIGNEE" Then
    '                    loai = 4
    '                    Continue For
    '                ElseIf NAME = "AGENT" Then
    '                    loai = 5
    '                    Continue For
    '                ElseIf NAME.Trim = "" Then
    '                    Continue For
    '                End If
    '            Catch ex As Exception
    '                Continue For
    '            End Try


    '            Select Case loai
    '                Case 1
    '                    Dim objCompany As New tblCompany
    '                    objCompany.Name = NAME
    '                    objCompany.Address = ADDRESS
    '                    objCompany.Tel = TEL
    '                    objCompany.Fax = FAX
    '                    objCompany.IsActive = True
    '                    objCompany.IsExport = 1

    '                    Dim m_com As New CompanyRepository
    '                    m_com.Insert(objCompany)

    '                    Dim customer As New tblCustomer
    '                    customer.CustomerName = cus
    '                    customer.IsActive = True
    '                    customer.ID_Company = objCompany.ID_Company
    '                    Dim m_cus As New CustomerRepository
    '                    m_cus.Insert(customer)
    '                Case 2
    '                    Dim objCompany As New tblCompany
    '                    objCompany.Name = NAME
    '                    objCompany.Address = ADDRESS
    '                    objCompany.Tel = TEL
    '                    objCompany.Fax = FAX
    '                    objCompany.IsActive = True
    '                    objCompany.IsExport = 0

    '                    Dim m_com As New CompanyRepository
    '                    m_com.Insert(objCompany)
    '                    Dim customer As New tblCustomer
    '                    customer.CustomerName = cus
    '                    customer.IsActive = True
    '                    customer.ID_Company = objCompany.ID_Company
    '                    Dim m_cus As New CustomerRepository
    '                    m_cus.Insert(customer)
    '                Case 3
    '                    Dim objCompany As New tblCompany
    '                    objCompany.Name = NAME
    '                    objCompany.Address = ADDRESS
    '                    objCompany.Tel = TEL
    '                    objCompany.Fax = FAX
    '                    objCompany.IsActive = True
    '                    objCompany.IsExport = 2

    '                    Dim m_com As New CompanyRepository
    '                    m_com.Insert(objCompany)
    '                    Dim customer As New tblCustomer
    '                    customer.CustomerName = cus
    '                    customer.IsActive = True
    '                    customer.ID_Company = objCompany.ID_Company
    '                    Dim m_cus As New CustomerRepository
    '                    m_cus.Insert(customer)
    '                Case 4
    '                    Dim objCompany As New tblForeignCompany
    '                    objCompany.Name = NAME
    '                    objCompany.Address = ADDRESS
    '                    objCompany.Tel = TEL
    '                    objCompany.Fax = FAX

    '                    Dim m_com As New CForeignCompany
    '                    m_com.Insert(objCompany)
    '                Case 5
    '                    Dim objCompany As New tblAgent
    '                    objCompany.Name = NAME
    '                    objCompany.Address = ADDRESS
    '                    objCompany.Tel = TEL
    '                    objCompany.Fax = FAX

    '                    Dim m_com As New CAgent
    '                    m_com.Insert(objCompany)
    '                Case Else

    '            End Select

    '            'Dim strSql As String
    '            'strSql = "INSERT INTO TBLSTORE(ID_STORE, TEN_STORE) VALUES (" & MA_STORE & ", N'" & TEN_STORE & "')"
    '            'BaseDB.ExecSql_NoneQuery(strSql)
    '        Next

    '        'For i As Integer = 4431 To 4665
    '        '    Dim MA_DONVI As Integer = 0
    '        '    Dim TEN_DONVI As String = ""
    '        '    MA_DONVI = osheetQTCB.Cells(i, 1).Value
    '        '    TEN_DONVI = osheetQTCB.Cells(i, 2).Value
    '        '    Dim strSql As String
    '        '    strSql = "INSERT INTO TBLDONVI(ID_DONVI, TEN_DONVI) VALUES (" & MA_DONVI & ", N'" & TEN_DONVI & "')"
    '        '    BaseDB.ExecSql_NoneQuery(strSql)
    '        'Next
    '        MsgBox("OK")


    '    End If
    'End Sub

    Public Shared Sub ToExcel_RECAP_IMPORT(ByVal ListFile() As tblFile)
        'SI FORM TSL
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\RECAP_IMPORT_template.xltx")
        xlSheet = xlBook.Worksheets("RECAP IMPORT")
        xlApp.Visible = True

        'KHOI TAO COLUMN BUYING AND SELLING
        Dim BFee = From p In db.tblFees
                        Where p.ID_FeeType = 1 And p.IsActive = True
                        Select p Order By p.Rank

        Dim SFee = From p In db.tblFees
                         Where p.ID_FeeType = 2 And p.IsActive = True
                         Select p Order By p.Rank

        Dim iColBuying As Integer = 18
        Dim iCol_TotalBuying As Integer

        For Each fee In BFee
            xlSheet.Cells(1, iColBuying) = fee.FeeName
            iColBuying += 1
        Next

        iCol_TotalBuying = iColBuying
        xlSheet.Cells(1, iColBuying) = "TOTAL BUYING"
        iColBuying += 1
        xlSheet.Cells(1, iColBuying) = "THANH TOÁN"
        iColBuying += 1

        mRange = xlSheet.Range(xlSheet.Cells(1, 18), xlSheet.Cells(1, iColBuying - 1))
        mRange.Interior.ColorIndex = 35

        Dim iColSelling As Integer = iColBuying
        Dim iCol_TotalSelling As Integer

        For Each fee In SFee
            xlSheet.Cells(1, iColBuying) = fee.FeeName
            iColBuying += 1
        Next

        iCol_TotalSelling = iColBuying
        xlSheet.Cells(1, iColBuying) = "TOTAL SELLING"
        iColBuying += 1
        xlSheet.Cells(1, iColBuying) = "THANH TOÁN"
        iColBuying += 1

        mRange = xlSheet.Range(xlSheet.Cells(1, iColSelling), xlSheet.Cells(1, iColBuying - 1))
        mRange.Interior.ColorIndex = 7

        Dim iRow0 As Integer = 2

        Dim i As Integer = 0
        For Each File In ListFile
            Dim ID_File As Integer = File.ID_File
            'Sỹ cắt chỗ  này

            'BUYING
            Dim Tong_Buying As Double = 0
            Dim FileFee = From p In db.tblFileFees
                          Where p.ID_File = ID_File
                          Select p

            Dim k As Integer = 0
            For Each fee In FileFee
                xlSheet.Cells(iRow0 + i, 18 + k) = fee.Price
                If fee.ID_Currency = 1 Then
                    Tong_Buying += fee.Price
                Else
                    If Not File.Ratio Is Nothing Then
                        Tong_Buying += fee.Price * File.Ratio
                    End If
                End If
                k += 1
            Next

            xlSheet.Cells(iRow0 + i, iCol_TotalBuying) = Tong_Buying
            mRange = xlSheet.Range(xlSheet.Cells(iRow0 + i, 18), xlSheet.Cells(iRow0 + i, iCol_TotalBuying))
            mRange.NumberFormatLocal = "#,##0"

            k += 1
            'THANH TOAN FILEFEE - ADVANCE VOUCHER
            '1. LAY CAC PHIEU TAM UNG CO LIEN QUAN DEN FILE
            '2. KIEM TRA CAC PHIEU TAM UNG DA LAP PHIEU THU CHUA?
            Dim List_ID_AV = (From p In db.tblFileFees
                             Where p.ID_File = ID_File
                             Select p.ID_AdvanceVoucher).Distinct.ToList

            Dim ListAV = From p In db.tblAdvanceVouchers
                          Where List_ID_AV.Contains(p.ID_AdvanceVoucher)
                          Select p

            Dim ThanhToan_Buying As String = ""
            Dim flagTT As Boolean = True

            For Each AV In ListAV
                If AV.ID_ReceiptVoucher Is Nothing Then
                    ThanhToan_Buying += AV.AdvanceVoucherNo + "-" + AV.CreatedDate + "Chưa TT" + Chr(10)
                    flagTT = False
                Else
                    ThanhToan_Buying += AV.AdvanceVoucherNo + "-" + AV.CreatedDate + Chr(10)
                End If
            Next

            If ThanhToan_Buying.Length > 0 Then
                ThanhToan_Buying = ThanhToan_Buying.Substring(0, ThanhToan_Buying.Length - 1)
            End If

            xlSheet.Cells(iRow0 + i, iCol_TotalBuying + 1) = ThanhToan_Buying

            If flagTT = False Then
                mRange = xlSheet.Range(xlSheet.Cells(iRow0 + i, iCol_TotalBuying + 1), xlSheet.Cells(iRow0 + i, iCol_TotalBuying + 1))
                mRange.Font.Color = 255
            End If

            'Tim HBL in file
            Dim ListHBL = From p In db.tblHBLs
                           Where p.tblFileMBL.ID_File = ID_File
                           Select p

            If ListHBL.Count = 0 Then
                'FILE NO
                xlSheet.Cells(iRow0 + i, 1) = File.FileNo

                'STAFF
                xlSheet.Cells(iRow0 + i, 2) = File.tblUser.UserName

                'CUSTOMER
                Try
                    xlSheet.Cells(iRow0 + i, 3) = File.tblCustomer.CustomerName
                Catch ex As Exception

                End Try

                'SHIPPER
                Try
                    xlSheet.Cells(iRow0 + i, 4) = File.tblCompany.Name
                Catch ex As Exception

                End Try
                'CONSIGNEE
                Try
                    xlSheet.Cells(iRow0 + i, 5) = File.tblForeignCompany.Name
                Catch ex As Exception

                End Try
                'AGENT
                Try
                    xlSheet.Cells(iRow0 + i, 6) = File.tblAgent.Name
                Catch ex As Exception

                End Try

                'MBL
                Dim MBL = (From p In db.tblMBLs
                          Where p.ID_MBL = (From q In db.tblFileMBLs Where q.ID_File = ID_File Select q.ID_MBL).SingleOrDefault
                          Select p).SingleOrDefault

                xlSheet.Cells(iRow0 + i, 8) = MBL.MBLNo
                'VESSEL
                Dim Voyage = (From p In db.tblVoyages
                             Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = MBL.ID_MBL Select q.ID_Voyage).SingleOrDefault
                             Select p).SingleOrDefault
                Try
                    xlSheet.Cells(iRow0 + i, 15) = Voyage.tblVessel.VesselName
                Catch ex As Exception

                End Try

                'POL
                Try
                    xlSheet.Cells(iRow0 + i, 12) = Voyage.tblPort.Port
                Catch ex As Exception

                End Try

                'ETD
                Try
                    xlSheet.Cells(iRow0 + i, 11) = CDate(Voyage.ETD).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try

                'ETA
                Try
                    xlSheet.Cells(iRow0 + i, 13) = CDate(Voyage.ETA).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try
                Try
                    'POD
                    xlSheet.Cells(iRow0 + i, 14) = Voyage.tblPort1.Port
                Catch ex As Exception

                End Try

                'QTY
                'xlSheet.Cells(iRow0 + i, 13) = Voyage.tblPort1.Port
                'QTy
                'xlSheet.Cells(iRow0 + i, 14) = Voyage.tblPort1.Port
                'LINE
                Try
                    xlSheet.Cells(iRow0 + i, 7) = Voyage.tblLine.LineName
                Catch ex As Exception

                End Try

                'TERM
                Try
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 2) = File.Term
                Catch ex As Exception

                End Try
                i += 1
            End If

            For Each HBL In ListHBL
                'Sỹ dán vào chỗ ni
                'FILE NO
                xlSheet.Cells(iRow0 + i, 1) = File.FileNo

                'STAFF
                xlSheet.Cells(iRow0 + i, 2) = File.tblUser.UserName

                'CUSTOMER
                Try
                    xlSheet.Cells(iRow0 + i, 3) = File.tblCustomer.CustomerName
                Catch ex As Exception

                End Try

                'SHIPPER
                Try
                    xlSheet.Cells(iRow0 + i, 4) = File.tblCompany.Name
                Catch ex As Exception

                End Try
                'CONSIGNEE
                Try
                    xlSheet.Cells(iRow0 + i, 5) = File.tblForeignCompany.Name
                Catch ex As Exception

                End Try
                'AGENT
                Try
                    xlSheet.Cells(iRow0 + i, 6) = File.tblAgent.Name
                Catch ex As Exception

                End Try

                'MBL
                Dim MBL = (From p In db.tblMBLs
                          Where p.ID_MBL = (From q In db.tblFileMBLs Where q.ID_File = ID_File Select q.ID_MBL).SingleOrDefault
                          Select p).SingleOrDefault

                xlSheet.Cells(iRow0 + i, 8) = MBL.MBLNo
                'VESSEL
                Dim Voyage = (From p In db.tblVoyages
                             Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = MBL.ID_MBL Select q.ID_Voyage).SingleOrDefault
                             Select p).SingleOrDefault
                Try
                    xlSheet.Cells(iRow0 + i, 15) = Voyage.tblVessel.VesselName
                Catch ex As Exception

                End Try

                'POL
                Try
                    xlSheet.Cells(iRow0 + i, 12) = Voyage.tblPort.Port
                Catch ex As Exception

                End Try

                'ETD
                Try
                    xlSheet.Cells(iRow0 + i, 11) = CDate(Voyage.ETD).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try

                'ETA
                Try
                    xlSheet.Cells(iRow0 + i, 13) = CDate(Voyage.ETA).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try
                Try
                    'POD
                    xlSheet.Cells(iRow0 + i, 14) = Voyage.tblPort1.Port
                Catch ex As Exception

                End Try

                'QTY
                'xlSheet.Cells(iRow0 + i, 13) = Voyage.tblPort1.Port
                'QTy
                'xlSheet.Cells(iRow0 + i, 14) = Voyage.tblPort1.Port
                'LINE
                Try
                    xlSheet.Cells(iRow0 + i, 7) = Voyage.tblLine.LineName
                Catch ex As Exception

                End Try

                'TERM
                Try
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 2) = File.Term
                Catch ex As Exception

                End Try

                'TỜ KHAI, NGÀY TỜ KHAI
                Try
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 3) = HBL.tblTaxReturn.TaxReturnNo
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 4) = HBL.tblTaxReturn.CreatedDate
                Catch ex As Exception

                End Try

                'HBL
                xlSheet.Cells(iRow0 + i, 9) = HBL.HBLNo

                'DECRIPTION OF GOOD
                xlSheet.Cells(iRow0 + i, 16) = HBL.GoodDescription
                'GW
                xlSheet.Cells(iRow0 + i, 17) = HBL.GW
                'CONT
                Dim ContName As String = ""

                Dim iD_HBL As Integer = HBL.ID_HBL
                Dim HBL_Cont = From p In db.tblConts
                               Where p.ID_HBL = iD_HBL
                               Select p

                For Each cont As tblCont In HBL_Cont
                    ContName += cont.ContNo + Chr(10)
                Next

                If ContName <> "" Then
                    ContName = ContName.Substring(0, ContName.Length - 1)
                End If

                xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 7) = ContName

                'SELLING
                Dim Tong_Selling As Double = 0

                Dim SellingFee = From p In db.tblSellingFees
                                 Where p.ID_HBL = iD_HBL
                                 Select p Order By p.ID_Fee Ascending

                Dim j As Integer = 0
                For Each fee In SellingFee
                    xlSheet.Cells(iRow0 + i, iColSelling + j) = fee.Price 'CDbl(fee.Price).ToString("#.###,0")
                    If fee.ID_Currency = 1 Then
                        Tong_Selling += fee.Price
                    Else
                        If Not HBL.Ratio Is Nothing Then
                            Tong_Selling += fee.Price * HBL.Ratio
                        End If
                    End If
                    j += 1
                Next
                xlSheet.Cells(iRow0 + i, iCol_TotalSelling) = Tong_Selling
                mRange = xlSheet.Range(xlSheet.Cells(iRow0 + i, iColSelling), xlSheet.Cells(iRow0 + i, iCol_TotalSelling))
                mRange.NumberFormatLocal = "#,##0"

                'THANH TOAN SELLING FEE - RECEIPT VOUCHER
                Dim RV_HBL = (From p In db.tblReceiptVouchers
                             Where p.ID_ReceiptVoucher = (From q In db.tblSellingFees Where q.ID_HBL = iD_HBL Select q.ID_ReceiptVoucher).Distinct.SingleOrDefault
                             Select p).SingleOrDefault
                If RV_HBL Is Nothing Then
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 1) = ""
                Else
                    Try
                        xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 1) = RV_HBL.PayDay
                    Catch ex As Exception

                    End Try
                End If


                i += 1
            Next
            'i += 1
        Next

        'FORMAT TOTAL COLUMN BUYING AND SELLING

        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalBuying), xlSheet.Cells(i, iCol_TotalBuying + 1))
        mRange.Interior.ColorIndex = 36 'light yellow

        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalSelling), xlSheet.Cells(i, iCol_TotalSelling + 1))
        mRange.Interior.ColorIndex = 36

        'FORMAT TERM
        xlSheet.Cells(1, iCol_TotalSelling + 2) = "TERM"
        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalSelling + 2), xlSheet.Cells(i, iCol_TotalSelling + 2))
        mRange.Interior.ColorIndex = 15 'GRAY

        'FORMAT TỜ KHAI THUẾ
        xlSheet.Cells(1, iCol_TotalSelling + 3) = "SỐ TK"
        xlSheet.Cells(1, iCol_TotalSelling + 4) = "NGÀY TK"
        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalSelling + 3), xlSheet.Cells(i, iCol_TotalSelling + 6))
        mRange.Interior.ColorIndex = 35 'GREEN
        mRange = xlSheet.Range(xlSheet.Cells(2, iCol_TotalSelling + 4), xlSheet.Cells(i, iCol_TotalSelling + 4))
        mRange.NumberFormat = "dd/mm/yyyy"
        'AMOUNT
        xlSheet.Cells(1, iCol_TotalSelling + 5) = "AMOUNT"
        'VAT
        xlSheet.Cells(1, iCol_TotalSelling + 6) = "VAT"
        'CONT
        xlSheet.Cells(1, iCol_TotalSelling + 7) = "CONT"

        'FORMAT NGÀY PHIẾU THU KHÁCH HÀNG
        mRange = xlSheet.Range(xlSheet.Cells(2, iCol_TotalSelling + 1), xlSheet.Cells(i, iCol_TotalSelling + 1))
        mRange.NumberFormat = "dd/mm/yyyy"

        'FORMAT NGÀY PHIẾU THU TẠM ỨNG
        mRange = xlSheet.Range(xlSheet.Cells(2, iCol_TotalSelling + 1), xlSheet.Cells(i, iCol_TotalSelling + 1))
        mRange.NumberFormat = "dd/mm/yyyy"
    End Sub

    Public Shared Sub ToExcel_RECAP_EXPORT(ByVal ListFile() As tblFile)
        'SI FORM TSL
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\RECAP_EXPORT_template.xltx")
        xlSheet = xlBook.Worksheets("RECAP EXPORT")
        xlApp.Visible = True

        'KHOI TAO COLUMN BUYING AND SELLING
        Dim BFee = From p In db.tblFees
                        Where p.ID_FeeType = 1 And p.IsActive = True
                        Select p Order By p.Rank

        Dim SFee = From p In db.tblFees
                         Where p.ID_FeeType = 2 And p.IsActive = True
                         Select p Order By p.Rank

        Dim iColBuying As Integer = 17
        Dim iCol_TotalBuying As Integer

        For Each fee In BFee
            xlSheet.Cells(1, iColBuying) = fee.FeeName
            iColBuying += 1
        Next

        iCol_TotalBuying = iColBuying
        xlSheet.Cells(1, iColBuying) = "TOTAL BUYING"
        iColBuying += 1
        xlSheet.Cells(1, iColBuying) = "THANH TOÁN"
        iColBuying += 1

        mRange = xlSheet.Range(xlSheet.Cells(1, 17), xlSheet.Cells(1, iColBuying - 1))
        mRange.Interior.ColorIndex = 35

        Dim iColSelling As Integer = iColBuying
        Dim iCol_TotalSelling As Integer

        For Each fee In SFee
            xlSheet.Cells(1, iColBuying) = fee.FeeName
            iColBuying += 1
        Next

        iCol_TotalSelling = iColBuying
        xlSheet.Cells(1, iColBuying) = "TOTAL SELLING"
        iColBuying += 1
        xlSheet.Cells(1, iColBuying) = "THANH TOÁN"
        iColBuying += 1

        mRange = xlSheet.Range(xlSheet.Cells(1, iColSelling), xlSheet.Cells(1, iColBuying - 1))
        mRange.Interior.ColorIndex = 7

        Dim iRow0 As Integer = 2

        Dim i As Integer = 0
        For Each File In ListFile
            Dim ID_File As Integer = File.ID_File
            'Sy cat
            'BUYING
            Dim Tong_Buying As Double = 0
            Dim FileFee = From p In db.tblFileFees
                          Where p.ID_File = ID_File
                          Select p

            Dim k As Integer = 0
            For Each fee In FileFee
                xlSheet.Cells(iRow0 + i, 17 + k) = fee.Price
                If fee.ID_Currency = 1 Then
                    Tong_Buying += fee.Price
                Else
                    If Not File.Ratio Is Nothing Then
                        Tong_Buying += fee.Price * File.Ratio
                    End If

                End If
                k += 1
            Next

            xlSheet.Cells(iRow0 + i, iCol_TotalBuying) = Tong_Buying
            mRange = xlSheet.Range(xlSheet.Cells(iRow0 + i, 17), xlSheet.Cells(iRow0 + i, iCol_TotalBuying))
            mRange.NumberFormatLocal = "#,##0"

            k += 1
            'THANH TOAN FILEFEE - ADVANCE VOUCHER
            '1. LAY CAC PHIEU TAM UNG CO LIEN QUAN DEN FILE
            '2. KIEM TRA CAC PHIEU TAM UNG DA LAP PHIEU THU CHUA?
            Dim List_ID_AV = (From p In db.tblFileFees
                             Where p.ID_File = ID_File
                             Select p.ID_AdvanceVoucher).Distinct.ToList

            Dim ListAV = From p In db.tblAdvanceVouchers
                          Where List_ID_AV.Contains(p.ID_AdvanceVoucher)
                          Select p

            Dim ThanhToan_Buying As String = ""
            Dim flagTT As Boolean = True

            For Each AV In ListAV
                If AV.ID_ReceiptVoucher Is Nothing Then
                    ThanhToan_Buying += AV.AdvanceVoucherNo + "-" + AV.CreatedDate + "Chưa TT" + Chr(10)
                    flagTT = False
                Else
                    ThanhToan_Buying += AV.AdvanceVoucherNo + "-" + AV.CreatedDate + Chr(10)
                End If
            Next

            If ThanhToan_Buying.Length > 0 Then
                ThanhToan_Buying = ThanhToan_Buying.Substring(0, ThanhToan_Buying.Length - 1)
            End If

            xlSheet.Cells(iRow0 + i, iCol_TotalBuying + 1) = ThanhToan_Buying

            If flagTT = False Then
                mRange = xlSheet.Range(xlSheet.Cells(iRow0 + i, iCol_TotalBuying + 1), xlSheet.Cells(iRow0 + i, iCol_TotalBuying + 1))
                mRange.Font.Color = 255
            End If

            'Tim HBL in file
            Dim ListHBL = From p In db.tblHBLs
                           Where p.tblFileMBL.ID_File = ID_File
                           Select p

            If ListHBL.Count = 0 Then
                'Sy chep vao cho ni
                'STAFF
                xlSheet.Cells(iRow0 + i, 2) = File.tblUser.UserName
                'FILE NO
                xlSheet.Cells(iRow0 + i, 3) = File.FileNo

                'CUSTOMER
                Try
                    xlSheet.Cells(iRow0 + i, 4) = File.tblCustomer.CustomerName
                Catch ex As Exception

                End Try

                'SHIPPER
                Try
                    xlSheet.Cells(iRow0 + i, 5) = File.tblCompany.Name
                Catch ex As Exception

                End Try

                'MBL
                Dim MBL = (From p In db.tblMBLs
                          Where p.ID_MBL = (From q In db.tblFileMBLs Where q.ID_File = ID_File Select q.ID_MBL).SingleOrDefault
                          Select p).SingleOrDefault

                xlSheet.Cells(iRow0 + i, 6) = MBL.MBLNo
                'VESSEL
                Dim Voyage = (From p In db.tblVoyages
                             Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = MBL.ID_MBL Select q.ID_Voyage).SingleOrDefault
                             Select p).SingleOrDefault
                Try
                    xlSheet.Cells(iRow0 + i, 8) = Voyage.tblVessel.VesselName
                Catch ex As Exception

                End Try

                'POL
                Try
                    xlSheet.Cells(iRow0 + i, 9) = Voyage.tblPort.Port
                Catch ex As Exception

                End Try

                'ETD
                Try
                    xlSheet.Cells(iRow0 + i, 10) = CDate(Voyage.ETD).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try

                'ETA
                Try
                    xlSheet.Cells(iRow0 + i, 11) = CDate(Voyage.ETA).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try
                Try
                    'POD
                    xlSheet.Cells(iRow0 + i, 12) = Voyage.tblPort1.Port
                Catch ex As Exception

                End Try

                'QTY
                'xlSheet.Cells(iRow0 + i, 13) = Voyage.tblPort1.Port
                'QTy
                'xlSheet.Cells(iRow0 + i, 14) = Voyage.tblPort1.Port
                'LINE
                'POD
                Try
                    xlSheet.Cells(iRow0 + i, 15) = Voyage.tblLine.LineName
                Catch ex As Exception

                End Try

                'TERM
                Try
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 2) = File.Term
                Catch ex As Exception

                End Try
                i += 1
            End If

            For Each HBL In ListHBL
                'Sy chep vao cho ni
                'STAFF
                xlSheet.Cells(iRow0 + i, 2) = File.tblUser.UserName
                'FILE NO
                xlSheet.Cells(iRow0 + i, 3) = File.FileNo

                'CUSTOMER
                Try
                    xlSheet.Cells(iRow0 + i, 4) = File.tblCustomer.CustomerName
                Catch ex As Exception

                End Try

                'SHIPPER
                Try
                    xlSheet.Cells(iRow0 + i, 5) = File.tblCompany.Name
                Catch ex As Exception

                End Try

                'MBL
                Dim MBL = (From p In db.tblMBLs
                          Where p.ID_MBL = (From q In db.tblFileMBLs Where q.ID_File = ID_File Select q.ID_MBL).SingleOrDefault
                          Select p).SingleOrDefault

                xlSheet.Cells(iRow0 + i, 6) = MBL.MBLNo
                'VESSEL
                Dim Voyage = (From p In db.tblVoyages
                             Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = MBL.ID_MBL Select q.ID_Voyage).SingleOrDefault
                             Select p).SingleOrDefault
                Try
                    xlSheet.Cells(iRow0 + i, 8) = Voyage.tblVessel.VesselName
                Catch ex As Exception

                End Try

                'POL
                Try
                    xlSheet.Cells(iRow0 + i, 9) = Voyage.tblPort.Port
                Catch ex As Exception

                End Try

                'ETD
                Try
                    xlSheet.Cells(iRow0 + i, 10) = CDate(Voyage.ETD).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try

                'ETA
                Try
                    xlSheet.Cells(iRow0 + i, 11) = CDate(Voyage.ETA).ToString("dd/MM/yyyy")
                Catch ex As Exception

                End Try
                Try
                    'POD
                    xlSheet.Cells(iRow0 + i, 12) = Voyage.tblPort1.Port
                Catch ex As Exception

                End Try

                'QTY
                'xlSheet.Cells(iRow0 + i, 13) = Voyage.tblPort1.Port
                'QTy
                'xlSheet.Cells(iRow0 + i, 14) = Voyage.tblPort1.Port
                'LINE
                'POD
                Try
                    xlSheet.Cells(iRow0 + i, 15) = Voyage.tblLine.LineName
                Catch ex As Exception

                End Try

                'TERM
                Try
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 2) = File.Term
                Catch ex As Exception

                End Try


                'TỜ KHAI, NGÀY TỜ KHAI
                Try
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 3) = HBL.tblTaxReturn.TaxReturnNo
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 4) = HBL.tblTaxReturn.CreatedDate
                Catch ex As Exception

                End Try

                'HBL
                xlSheet.Cells(iRow0 + i, 7) = HBL.HBLNo

                'CONT
                Dim ContName As String = ""

                Dim iD_HBL As Integer = HBL.ID_HBL
                Dim HBL_Cont = From p In db.tblConts
                               Where p.ID_HBL = iD_HBL
                               Select p

                For Each cont As tblCont In HBL_Cont
                    ContName += cont.ContNo + Chr(10)
                Next
                If ContName <> "" Then
                    ContName = ContName.Substring(0, ContName.Length - 1)
                End If

                xlSheet.Cells(iRow0 + i, 16) = ContName

                'SELLING
                Dim Tong_Selling As Double = 0

                Dim SellingFee = From p In db.tblSellingFees
                                 Where p.ID_HBL = iD_HBL
                                 Select p Order By p.ID_Fee Ascending

                Dim j As Integer = 0
                For Each fee In SellingFee
                    xlSheet.Cells(iRow0 + i, iColSelling + j) = fee.Price 'CDbl(fee.Price).ToString("#.###,0")
                    If fee.ID_Currency = 1 Then
                        Tong_Selling += fee.Price
                    Else
                        If Not HBL.Ratio Is Nothing Then
                            Tong_Selling += fee.Price * HBL.Ratio
                        End If
                    End If
                    j += 1
                Next
                xlSheet.Cells(iRow0 + i, iCol_TotalSelling) = Tong_Selling
                mRange = xlSheet.Range(xlSheet.Cells(iRow0 + i, iColSelling), xlSheet.Cells(iRow0 + i, iCol_TotalSelling))
                mRange.NumberFormatLocal = "#,##0"

                'THANH TOAN SELLING FEE - RECEIPT VOUCHER
                Dim RV_HBL = (From p In db.tblReceiptVouchers
                             Where p.ID_ReceiptVoucher = (From q In db.tblSellingFees Where q.ID_HBL = iD_HBL Select q.ID_ReceiptVoucher).Distinct.SingleOrDefault
                             Select p).SingleOrDefault
                If RV_HBL Is Nothing Then
                    xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 1) = ""
                Else
                    Try
                        xlSheet.Cells(iRow0 + i, iCol_TotalSelling + 1) = RV_HBL.PayDay
                    Catch ex As Exception

                    End Try
                End If


                i += 1
            Next
            'i += 1
        Next

        'FORMAT TOTAL COLUMN BUYING AND SELLING

        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalBuying), xlSheet.Cells(i, iCol_TotalBuying + 1))
        mRange.Interior.ColorIndex = 36 'light yellow

        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalSelling), xlSheet.Cells(i, iCol_TotalSelling + 1))
        mRange.Interior.ColorIndex = 36

        'FORMAT TERM
        xlSheet.Cells(1, iCol_TotalSelling + 2) = "TERM"
        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalSelling + 2), xlSheet.Cells(i, iCol_TotalSelling + 2))
        mRange.Interior.ColorIndex = 15 'GRAY

        'FORMAT TỜ KHAI THUẾ
        xlSheet.Cells(1, iCol_TotalSelling + 3) = "SỐ TK"
        xlSheet.Cells(1, iCol_TotalSelling + 4) = "NGÀY TK"
        mRange = xlSheet.Range(xlSheet.Cells(1, iCol_TotalSelling + 3), xlSheet.Cells(i, iCol_TotalSelling + 4))
        mRange.Interior.ColorIndex = 35 'GREEN
        mRange = xlSheet.Range(xlSheet.Cells(2, iCol_TotalSelling + 4), xlSheet.Cells(i, iCol_TotalSelling + 4))
        mRange.NumberFormat = "dd/mm/yyyy"

        'FORMAT NGÀY PHIẾU THU KHÁCH HÀNG
        mRange = xlSheet.Range(xlSheet.Cells(2, iCol_TotalSelling + 1), xlSheet.Cells(i, iCol_TotalSelling + 1))
        mRange.NumberFormat = "dd/mm/yyyy"

        'FORMAT NGÀY PHIẾU THU TẠM ỨNG
        mRange = xlSheet.Range(xlSheet.Cells(2, iCol_TotalSelling + 1), xlSheet.Cells(i, iCol_TotalSelling + 1))
        mRange.NumberFormat = "dd/mm/yyyy"
    End Sub

    Public Shared Sub ToExcel_DetailBooking(ByVal ID_HBL As Integer)
        'SI FORM TSL
        Dim db As New TanNamChinhDataContext

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim mRange As Excel.Range

        xlApp = New Excel.Application
        xlBook = xlApp.Workbooks.Open(Application.StartupPath & "\SI FORM_TSL_template.xltx")
        xlSheet = xlBook.Worksheets("SI")
        xlApp.Visible = True

        Dim HBL = (From p In db.tblHBLs
                    Where p.ID_HBL = ID_HBL
                    Select p).Single

        'Get File
        Dim File = (From p In db.tblFiles
                       Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
                       Select p).SingleOrDefault

        Dim Shipper As String = ""
        Dim Customer As String = ""

        '0 BOOKING NUMBER
        xlSheet.Cells(5, 7) = File.LineBookingNo

        If HBL.IsMaster Then
            'MBL
            '1. SHIPPER
            xlSheet.Cells(6, 2) = File.tblCompany.Name
            xlSheet.Cells(7, 2) = "Address:" + File.tblCompany.Address
            xlSheet.Cells(8, 2) = "Tel: " + File.tblCompany.Tel
            xlSheet.Cells(9, 2) = "Fax: " + File.tblCompany.Fax
            Shipper = File.tblCompany.Name + Chr(10) + File.tblCompany.Address + Chr(10) + "TEL: " + File.tblCompany.Tel + " - FAX: " + File.tblCompany.Fax

            '2. CONSIGNEE ' TBLFOREIGNCOMPANY
            xlSheet.Cells(14, 2) = File.tblForeignCompany.Name
            xlSheet.Cells(15, 2) = "Address:" + File.tblForeignCompany.Address
            xlSheet.Cells(16, 2) = "Tel: " + File.tblForeignCompany.Tel
            xlSheet.Cells(17, 2) = "Fax: " + File.tblForeignCompany.Fax

        Else
            'HBL SHIPPER = TAN NAM CHINH
            '1. SHIPPER
            xlSheet.Cells(6, 2) = "TAN NAM CHINH TRADING AND SERVICE CO., LTD (TANACHI CO., LTD)"
            xlSheet.Cells(7, 2) = "Address: 483/67 LE VAN SY ST., DIST 3, HOCHIMINH CITY, VIETNAM"
            xlSheet.Cells(8, 2) = "Tel: 848-39432369 / 70"
            xlSheet.Cells(9, 2) = "Fax : 848-38265731"

            Shipper = "TAN NAM CHINH TRADING AND SERVICE CO., LTD (TANACHI CO., LTD)" + Chr(10) + _
                         "483/67 LE VAN SY ST., DIST 3, HOCHIMINH CITY, VIETNAM" + Chr(10) + _
                         "TEL: 848-39432369 / 70 - FAX : 848-38265731"

            '2. CONSIGNEE ' TBLAGENT
            xlSheet.Cells(14, 2) = File.tblAgent.Name
            xlSheet.Cells(15, 2) = "Address:" + File.tblAgent.Address
            xlSheet.Cells(16, 2) = "Tel: " + File.tblAgent.Tel
            xlSheet.Cells(17, 2) = "Fax: " + File.tblAgent.Fax
        End If

        '3. NOTIFY PARTY
        Dim NotifyParty As String() = HBL.NotifyParty.Split(Chr(10))
        Try
            xlSheet.Cells(14, 6) = NotifyParty(0)
            xlSheet.Cells(15, 6) = NotifyParty(1)
            xlSheet.Cells(16, 6) = NotifyParty(2)
            xlSheet.Cells(17, 6) = NotifyParty(3)
        Catch ex As Exception

        End Try

        '5. VESSEL/VOYAGE
        Dim Voyage = (From p In db.tblVoyages
                     Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = HBL.tblFileMBL.tblMBL.ID_MBL Select q.ID_Voyage).Single
                     Select p).SingleOrDefault
        Try
            xlSheet.Cells(24, 2) = Voyage.tblVessel.VesselName + "/" + Voyage.VoyageNo
        Catch ex As Exception

        End Try

        Try
            'POD
            xlSheet.Cells(24, 4) = Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName
        Catch ex As Exception

        End Try
        
        ''Place of Receipt
        'xlSheet.Cells(22, 4) = Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        Try
            'POL
            xlSheet.Cells(22, 6) = Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        Catch ex As Exception

        End Try
        
        ''Place of Delivery
        'xlSheet.Cells(24, 6) = Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName
        'xlSheet.Cells(21, 1) = "ETD: " + Voyage.ETD.Value.Month.ToString + " " + Voyage.ETD.Value.Day.ToString + " " + Voyage.ETD.Value.Year.ToString
        'xlSheet.Cells(22, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
        'xlSheet.Cells(21, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName

        '6. MARK NUMBER
        xlSheet.Cells(26, 2) = HBL.MarkNumber
        mRange = xlSheet.Range(xlSheet.Cells(26, 2), xlSheet.Cells(39, 2))
        mRange.Merge()
        mRange.WrapText = True

        '7 DECRIPTION OF GOOD
        xlSheet.Cells(26, 4) = HBL.GoodDescription
        mRange = xlSheet.Range(xlSheet.Cells(26, 4), xlSheet.Cells(39, 4))
        mRange.Merge()
        mRange.WrapText = True

        'GW - NW
        xlSheet.Cells(26, 7) = CDbl(HBL.GW).ToString("#,###")
        xlSheet.Cells(26, 8) = CDbl(HBL.NW).ToString("#,###")
        xlSheet.Cells(26, 9) = CDbl(HBL.CBM).ToString("#,###")

        '6. CONT/SEAL/CONTTYPE
        Dim HBL_Cont = From p In db.tblConts
                       Where p.ID_HBL = HBL.ID_HBL
                       Select p

        Dim NumInsert As Integer = 0
        If HBL_Cont.Count > 13 Then
            NumInsert = HBL_Cont.Count - 13
        End If

        For j As Integer = 1 To NumInsert
            xlSheet.Rows(42).Insert()
        Next


        Dim i As Integer = 0
        For Each cont As tblCont In HBL_Cont
            xlSheet.Cells(41 + i, 2) = i + 1
            xlSheet.Cells(41 + i, 3) = cont.ContNo.ToUpper
            xlSheet.Cells(41 + i, 4) = cont.tblContType.ContTypeName.ToUpper
            xlSheet.Cells(41 + i, 5) = cont.Seal.ToUpper
            xlSheet.Cells(41 + i, 6) = cont.GW
            xlSheet.Cells(41 + i, 7) = cont.NW
            xlSheet.Cells(41 + i, 8) = cont.CBM
            Try
                xlSheet.Cells(41 + i, 9) = cont.Package
                xlSheet.Cells(41 + i, 10) = cont.tblUnit.UnitName
            Catch ex As Exception

            End Try
            mRange = xlSheet.Range(xlSheet.Cells(41 + i, 6), xlSheet.Cells(41 + i, 9))
            mRange.NumberFormatLocal = "#,###"
            i += 1
        Next
        
    End Sub

    Public Shared Function NumToStringEnglish(ByVal nNumber As Double) As String
        Dim ChuSo As String = ""
        Dim bNegative As Boolean
        Dim bHundred As Boolean

        If nNumber < 0 Then
            bNegative = True
        End If

        nNumber = Math.Abs(nNumber)

        If nNumber < 1000 Then
            If nNumber \ 100 > 0 Then
                ChuSo = ChuSo & _
                     NumToStringEnglish(nNumber \ 100) & " hundred"
                bHundred = True
            End If
            nNumber = nNumber - ((nNumber \ 100) * 100)
            Dim bNoFirstDigit As Boolean
            bNoFirstDigit = False
            Select Case nNumber \ 10
                Case 0
                    Select Case nNumber Mod 10
                        Case 0
                            If Not bHundred Then
                                ChuSo = ChuSo & " zero"
                            End If
                        Case 1 : ChuSo = ChuSo & " one"
                        Case 2 : ChuSo = ChuSo & " two"
                        Case 3 : ChuSo = ChuSo & " three"
                        Case 4 : ChuSo = ChuSo & " four"
                        Case 5 : ChuSo = ChuSo & " five"
                        Case 6 : ChuSo = ChuSo & " six"
                        Case 7 : ChuSo = ChuSo & " seven"
                        Case 8 : ChuSo = ChuSo & " eight"
                        Case 9 : ChuSo = ChuSo & " nine"
                    End Select
                    bNoFirstDigit = True
                Case 1
                    Select Case nNumber Mod 10
                        Case 0 : ChuSo = ChuSo & " ten"
                        Case 1 : ChuSo = ChuSo & " eleven"
                        Case 2 : ChuSo = ChuSo & " twelve"
                        Case 3 : ChuSo = ChuSo & " thirteen"
                        Case 4 : ChuSo = ChuSo & " fourteen"
                        Case 5 : ChuSo = ChuSo & " fifteen"
                        Case 6 : ChuSo = ChuSo & " sixteen"
                        Case 7 : ChuSo = ChuSo & " seventeen"
                        Case 8 : ChuSo = ChuSo & " eighteen"
                        Case 9 : ChuSo = ChuSo & " nineteen"
                    End Select
                    bNoFirstDigit = True
                Case 2 : ChuSo = ChuSo & " twenty"
                Case 3 : ChuSo = ChuSo & " thirty"
                Case 4 : ChuSo = ChuSo & " forty"
                Case 5 : ChuSo = ChuSo & " fifty"
                Case 6 : ChuSo = ChuSo & " sixty"
                Case 7 : ChuSo = ChuSo & " seventy"
                Case 8 : ChuSo = ChuSo & " eighty"
                Case 9 : ChuSo = ChuSo & " ninety"
            End Select
            If Not bNoFirstDigit Then
                If nNumber Mod 10 <> 0 Then
                    ChuSo = ChuSo & "-" & _
                                  Mid(NumToStringEnglish(nNumber Mod 10), 2)
                End If
            End If
        Else
            Dim nTemp As Double
            nTemp = 10 ^ 12 'trillion
            Do While nTemp >= 1
                If nNumber >= nTemp Then
                    ChuSo = ChuSo & _
                                  NumToStringEnglish(Math.Floor(nNumber / nTemp)) 'Sy sua
                    Select Case CInt(Math.Log(nTemp) / Math.Log(10) + 0.5)
                        Case 12 : ChuSo = ChuSo & " trillion"
                        Case 9 : ChuSo = ChuSo & " billion"
                        Case 6 : ChuSo = ChuSo & " million"
                        Case 3 : ChuSo = ChuSo & " thousand"
                    End Select

                    nNumber = nNumber - (Math.Floor(nNumber / nTemp) * nTemp)
                End If
                nTemp = nTemp / 1000
            Loop
        End If

        If bNegative Then
            ChuSo = " negative" & ChuSo
        End If
        Return ChuSo
    End Function



#Region "DocSoRaChu"
    Public Shared Function docchu(ByVal so1 As Double) As String

        Dim tram As Integer, chuc As Integer, dv As Integer, so As Integer
        Dim chu(0 To 9) As String, chuoi1, chuoi2, chuoi
        so = so1
        chu(0) = "không"
        chu(1) = "một"
        chu(2) = "hai"
        chu(3) = "ba"
        chu(4) = "bốn"
        chu(5) = "năm"
        chu(6) = "sáu"
        chu(7) = "bảy"
        chu(8) = "tám"
        chu(9) = "chín"
        tram = so \ 100
        so = so Mod 100
        chuc = so \ 10
        dv = so Mod 10
        Select Case chuc
            Case 0
                chuoi2 = IIf(tram = 0, "", IIf(dv = 0, "", "lẻ"))
            Case 1
                chuoi2 = "mười"
            Case Else
                chuoi2 = chu(chuc) + " mươi"
        End Select
        Select Case tram
            Case 0
                chuoi1 = ""
            Case Else
                chuoi1 = chu(tram) + " trăm "
        End Select
        Select Case dv
            Case 0
                chuoi = ""
            Case 1
                chuoi = IIf((chuoi2 = "") Or (chuoi2 = "mười") Or (chuoi2 = "lẻ"), "một", "mốt")
            Case 5
                chuoi = IIf((chuoi2 = "lẻ") Or (chuoi2 = ""), "năm", "lăm")
            Case Else
                chuoi = chu(dv)
        End Select

        docchu = Trim(chuoi1 + IIf(chuoi2 <> "", chuoi2, "") + " " + chuoi)
    End Function

    Public Shared Function doc(ByVal so1 As Double) As String
        Dim chuoi1, chuoi2, chuoi3, chuoi4 As String
        chuoi1 = ""
        If so1 = 0 Then
            doc = "không"
            Exit Function
        End If
        Dim so As Double
        so = so1
        Dim ty As Integer, trieu As Integer, ngan As Integer, dv As Integer, rr As Double
        ty = Int(so / 1000000000)
        rr = ty
        so = so - CDbl(1000000000) * CDbl(ty)
        trieu = so \ 1000000
        so = so Mod 1000000
        ngan = so \ 1000
        dv = so Mod 1000
        If ty > 1000 Then
            chuoi1 = doc(rr) + " tỷ"
        ElseIf ty > 0 Then
            chuoi1 = docchu(ty) + " tỷ"
        End If
        chuoi2 = IIf(trieu > 0, docchu(trieu) + " triệu", "")
        chuoi3 = IIf(ngan > 0, docchu(ngan) + " ngàn", "")
        chuoi4 = IIf(dv > 0, docchu(dv), "")
        doc = Trim(chuoi1 + IIf(chuoi2 <> "", " " + chuoi2, "") + IIf(chuoi3 <> "", " " + chuoi3, "") + IIf(chuoi4 <> "", " " + chuoi4, ""))
    End Function

    Public Shared Function NumToStringVietNam(ByVal so As Double)
        Dim so1 As Double, le As String, chuoi As String
        'chuoi = Format(so, "000000000000000000.00")
        so1 = Math.Round(so) 'Val(Left(chuoi, Len(chuoi) - 3))
        le = 0 'Right(chuoi, 2)
        If Val(le) > 0 Then
            If Val(le) < 10 Then
                chuoi = doc(so1) + " phẩy không " + doc(Val(le))
            Else
                chuoi = doc(so1) + " phẩy " + doc(Val(le))
            End If
        Else
            chuoi = doc(so1)
        End If
        If (chuoi.Length <> 0) Then
            Dim c As String
            c = chuoi.Substring(0, 1)
            chuoi = c.ToUpper() + chuoi.Substring(1)
        End If
        NumToStringVietNam = chuoi + " đồng"
    End Function
#End Region

#Region "Detail Bill"
    'Dim db As New TanNamChinhDataContext

    'Dim mFileName2 As String = Application.StartupPath & "\TempDETAILBOOKING.xls"
    'Dim mFileName As String = Application.StartupPath & "\DETAILBOOKING.xls"
    'Try
    '    System.IO.File.Copy(mFileName, mFileName2, True)
    'Catch ex As Exception
    '    MsgBox("File Excel TempDetailBooking đang được sử dụng.")
    '    Exit Sub
    'End Try

    'Dim xlApp As Excel.Application
    'Dim xlBook As Excel.Workbook
    'Dim xlSheet As Excel.Worksheet
    'Dim mRange As Excel.Range
    'xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
    'xlBook = CType(xlApp.Workbooks.Open(mFileName2), Excel.Workbook)


    'xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
    'xlSheet.Activate()
    'xlSheet.Application.Visible = True

    'Dim HBL = (From p In db.tblHBLs
    '            Where p.ID_HBL = ID_HBL
    '            Select p).Single

    ''Get File
    'Dim File = (From p In db.tblFiles
    '               Where p.ID_File = (From q In db.tblFileMBLs Where q.ID_FileMBL = HBL.ID_FileMBL Select q.ID_File).Single
    '               Select p).SingleOrDefault

    ''1. HEADER
    'xlSheet.Cells(1, 1) = "DETAIL BILL FOR BOOKING " + HBL.HBLNo
    'If File.IsD2D Then
    '    xlSheet.Cells(7, 5) = "D // D"
    'Else
    '    xlSheet.Cells(7, 5) = "CY // CY"
    'End If

    ''2. SHIPPER

    ''3. CONSIGNEE
    'xlSheet.Cells(11, 1) = File.tblCompany.Name
    'xlSheet.Cells(12, 1) = File.tblCompany.Address
    'xlSheet.Cells(13, 1) = "Tel: " + File.tblCompany.Tel
    'xlSheet.Cells(14, 1) = "Fax: " + File.tblCompany.Fax



    ''4. NOTIFY PARTY
    'xlSheet.Cells(18, 1) = HBL.NotifyParty

    ''5. VESSEL
    'Dim Voyage = (From p In db.tblVoyages
    '             Where p.ID_Voyage = (From q In db.tblMBLs Where q.ID_MBL = HBL.tblFileMBL.tblMBL.ID_MBL Select q.ID_Voyage).Single
    '             Select p).SingleOrDefault

    'xlSheet.Cells(20, 1) = "VSSL: " + Voyage.tblVessel.VesselName + " " + Voyage.VoyageNo
    'xlSheet.Cells(21, 1) = "ETD: " + Voyage.ETD.Value.Month.ToString + " " + Voyage.ETD.Value.Day.ToString + " " + Voyage.ETD.Value.Year.ToString
    'xlSheet.Cells(22, 1) = "POL: " + Voyage.tblPort.Port + ", " + Voyage.tblPort.tblCountry.CountryName
    'xlSheet.Cells(21, 1) = "POD: " + Voyage.tblPort1.Port + ", " + Voyage.tblPort1.tblCountry.CountryName

    ''6. CONT/SEAL/CONTTYPE
    'Dim HBL_Cont = From p In db.tblConts
    '               Where p.ID_HBL = HBL.ID_HBL
    '               Select p

    'Dim i As Integer = 0
    'For Each cont As tblCont In HBL_Cont
    '    xlSheet.Cells(26 + i, 1) = cont.ContNo.ToUpper + "/" + cont.Seal.ToUpper + "/" + cont.tblContType.ContTypeName.ToUpper
    '    mRange = xlSheet.Range(xlSheet.Cells(26 + 1, 1), xlSheet.Cells(26 + i, 1))
    '    mRange.Font.Bold = True
    '    mRange.Font.Italic = True
    '    i += 1
    'Next

    ''7. ĐUÔI PHIẾU
    'Dim Cont_ContType As String = ""
    'Dim Say As String = ""
    'Dim ListCont = From p In db.tblConts
    '               Join q In db.tblContTypes On p.ID_ContType Equals q.ID_ContType
    '               Where p.ID_HBL = HBL.ID_HBL
    '               Group p By p.ID_ContType, q.ContTypeName Into Group
    '               Select New With
    '                      {
    '                          .ID_ContType = ID_ContType,
    '                          .ContTypeName = ContTypeName,
    '                          .Count = Group.Count
    '                      }

    'For Each cont In ListCont
    '    If cont.Count < 10 Then
    '        If cont.ContTypeName.Substring(1, 2) = "20" Then
    '            Say += " &" + NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "TWENTY-FEET CONTAINER(S)"
    '        ElseIf cont.ContTypeName.Substring(1, 2) = "40" Then
    '            Say += " &" + NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "FORTY-FEET CONTAINER(S)"
    '        Else
    '            Say += " &" + NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "?-FEET CONTAINER(S)"
    '        End If
    '        Cont_ContType += " & " + "(0" + cont.Count.ToString + ")" + cont.ContTypeName
    '    Else
    '        If cont.ContTypeName.Substring(1, 2) = "20" Then
    '            Say += " &" + NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "TWENTY-FEET CONTAINER(S)"
    '        ElseIf cont.ContTypeName.Substring(1, 2) = "40" Then
    '            Say += " &" + NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "FORTY-FEET CONTAINER(S)"
    '        Else
    '            Say += " &" + NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "?-FEET CONTAINER(S)"
    '        End If
    '        Cont_ContType += " & " + "(" + cont.Count.ToString + ")" + cont.ContTypeName
    '    End If
    'Next
    'Cont_ContType = Cont_ContType.Substring(3, Cont_ContType.Length - 3)
    'Say = Say.Substring(3, Say.Length - 3)
    'Say = "SAY: " + Say

    'If File.IsFCL Then
    '    Cont_ContType = Cont_ContType + " (FCL) S.T.C"
    'Else
    '    Cont_ContType = Cont_ContType + " (LCL) S.T.C"
    'End If

    'If File.IsD2D Then
    '    Cont_ContType = Cont_ContType + "  D/D"
    'Else ' CY/CY
    '    Cont_ContType = Cont_ContType + "  CY/CY"
    'End If

    'xlSheet.Cells(28, 3) = Cont_ContType
    'xlSheet.Cells(29, 5) = CDbl(HBL.GW).ToString("#,###") + "KGS"

    ''8 SAYSAY: ONE (01) FORTY-FEET CONTAINER(S) ONLY
    'xlSheet.Cells(42, 3) = Say

    ''TÊN ĐƠN VỊ
    'Dim m_Company As New CompanyRepository
    'Dim obj_Company = m_Company.SelectRecordById(File.ID_Company)

    'xlSheet.Cells(8, 3) = obj_Company.CompanyName
    'xlSheet.Cells(9, 3) = obj_Company.Address
    'xlSheet.Cells(10, 3) = obj_Company.TaxNo

    ''BILL
    'Dim i As Integer = 0

    'For Each m_sf As tblSellingFee In sf
    '    xlSheet.Cells(11 + i, 3) = m_sf.tblFee.FeeName
    '    xlSheet.Cells(11 + i, 4) = m_sf.Price
    '    xlSheet.Cells(11 + i, 5) = m_sf.tblCurrency.Code

    '    i += 1
    'Next

    ''TỔNG CỘNG
    'xlSheet.Cells(11 + i, 1) = "TỔNG "
    'xlSheet.Cells(11 + i, 2) = ":"
    'xlSheet.Cells(11 + i, 4) = txtTongTien.Text
    'xlSheet.Cells(11 + i, 5) = "VND"
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i, 3), xlSheet.Cells(11 + i, 9))
    'mRange.Font.Bold = True

    ''BẰNG CHỮ
    'xlSheet.Cells(11 + i + 1, 1) = "BẰNG CHỮ "
    'xlSheet.Cells(11 + i + 1, 2) = ":"
    'xlSheet.Cells(11 + i + 1, 3) = DoiSoRaChu(CDbl(txtTongTien.Text))

    ''NỘI DUNG
    'xlSheet.Cells(11 + i + 2, 1) = "NỘI DUNG "
    'xlSheet.Cells(11 + i + 2, 2) = ":"
    'xlSheet.Cells(11 + i + 2, 3) = txtNoiDungThu.Text


    ''ĐUÔI PHIẾU
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 1), xlSheet.Cells(11 + i + 4, 9))
    'mRange.Font.Bold = True
    'mRange.Font.Size = 11
    'mRange.Font.Italic = True
    ''NGƯỜI LẬP PHIẾU


    'xlSheet.Cells(11 + i + 4, 1) = "NGƯỜI LẬP PHIẾU "
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 1), xlSheet.Cells(11 + i + 4, 3))
    'mRange.Merge()
    'mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    'xlSheet.Cells(11 + i + 4, 4) = "THỦ QUỸ"
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 4), xlSheet.Cells(11 + i + 4, 5))
    'mRange.Merge()
    'mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    'xlSheet.Cells(11 + i + 4, 7) = "NGƯỜI NỘP TIỀN"
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 4, 7), xlSheet.Cells(11 + i + 4, 9))
    'mRange.Merge()
    'mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter


    ''HỌ TÊN
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 1), xlSheet.Cells(11 + i + 8, 9))
    'mRange.Font.Bold = True
    'mRange.Font.Size = 11

    'xlSheet.Cells(11 + i + 8, 1) = txtNguoiLapPhieu.Text.ToUpper
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 1), xlSheet.Cells(11 + i + 8, 3))
    'mRange.Merge()
    'mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    'xlSheet.Cells(11 + i + 8, 4) = "THỦ QUỸ"
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 4), xlSheet.Cells(11 + i + 8, 5))
    'mRange.Merge()
    'mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    'xlSheet.Cells(11 + i + 8, 7) = txtNguoiNopTien.Text.ToUpper
    'mRange = xlSheet.Range(xlSheet.Cells(11 + i + 8, 7), xlSheet.Cells(11 + i + 8, 9))
    'mRange.Merge()
    'mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

    ''FORMAT GẠCH DƯỚI
    'For j As Integer = 8 To 13 + i
    '    mRange = xlSheet.Range(xlSheet.Cells(j, 3), xlSheet.Cells(j, 9))
    '    With mRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
    '        .LineStyle = Excel.XlLineStyle.xlDot
    '        .Weight = Excel.XlBorderWeight.xlHairline
    '    End With
    'Next
#End Region

End Class
